/********************************************
 * 功能说明: 
 * 模块名称: 
 * 系统名称: 
 * 软件版权: 
 * 系统版本: 1.0.0
 * 开发人员: zhangfb
 * 开发时间: 2019/3/31 15:46
 * 审核人员: 
 * 相关文档: 
 * 修改记录: 修改日期 修改人员 修改说明
 *********************************************/
package com.hyacinth.meta;

import com.google.common.annotations.VisibleForTesting;
import com.hyacinth.config.DBType;
import com.hyacinth.meta.ColumnMetaData;
import com.hyacinth.meta.ForeignKeyMetaData;
import com.hyacinth.meta.PrimaryKeyMetaData;
import com.hyacinth.meta.TableMetaData;
import com.hyacinth.support.DBOptionProcessor;
import com.hyacinth.utils.Assert;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * 元数据基本操作类
 * @author zhangfb
 * @version 1.0.0.1
 * @since JDK 1.8
 */
public class DatabaseAnalyzer {

    private static final Logger LOGGER = LoggerFactory.getLogger(DBOptionProcessor.class);

    public static List<TableMetaData> getTablesSimple(Connection connection, String dbName) throws SQLException {
        Assert.isTrue(!connection.isClosed(), "connection is closed");
        DatabaseMetaData dbMeta = connection.getMetaData();
        List<TableMetaData> tables = new LinkedList<>();

        String driverName = connection.getMetaData().getDriverName();
        //需要判断数据库类型，组装不同的查询条件
        if (StringUtils.startsWithIgnoreCase(driverName, "Oracle")) {
            dbName = dbMeta.getUserName();
        } else if (StringUtils.startsWithIgnoreCase(driverName, "Microsoft")) {
            dbName = null;
        }

        ResultSet tablesResultSet = dbMeta.getTables(connection.getCatalog(), dbName, null, new String[]{"TABLE"});
        while (tablesResultSet.next()) {
            String tableName = tablesResultSet.getString("TABLE_NAME");
            String remark = tablesResultSet.getString("REMARKS");
            tables.add(new TableMetaData(tableName, remark));
        }
        return tables;
    }

    /**
     * 获取表数据库元数据集合
     * @param connection 链接
     * @param dbType 数据库类型
     * @return 元数据集合
     * @throws SQLException sql 异常
     */
    public static List<TableMetaData> getTables(Connection connection, DBType dbType) throws SQLException {
        Assert.isTrue(!connection.isClosed(), "connection is closed");
        String dbName = connection.getCatalog();
        List<TableMetaData> tables = new LinkedList<>();
        // 获取数据库元数据信息
        DatabaseMetaData dbMeta = connection.getMetaData();
        // 获取表结果集
        ResultSet resultSet = dbMeta.getTables(dbName, null, null, new String[]{"TABLE"});
        // 遍历结果集
        if (resultSet.next()) {
            // 获取表名称
            String tableName = resultSet.getString("TABLE_NAME");
            // 获取表备注
            String tableRemark = resultSet.getString("REMARKS");
            // 获取列信息
            List<ColumnMetaData> columns = getColumns(dbMeta, dbName, tableName, dbType);
            // 获取主键信息
            List<PrimaryKeyMetaData> primaryKeys = getPrimaryKeys(dbMeta, dbName, tableName);
            // 获取外键信息
            List<ForeignKeyMetaData> foreignKeys = getForeignKeys(dbMeta, dbName, tableName);
            tables.add(new TableMetaData(tableName, tableRemark, columns, primaryKeys, foreignKeys));
        }
        return tables;
    }

    public static List<ColumnMetaData> getColumns(DatabaseMetaData dbMeta, String dbName, String tableName, DBType dbType) throws SQLException {
        List<ColumnMetaData> columns = new LinkedList<ColumnMetaData>();
        if (dbType == DBType.ORACLE) {
            dbName = dbMeta.getUserName();
        } else if (dbType != DBType.DAMENG) {
            dbName = "%";
        }
        String userName = dbMeta.getUserName();
        ResultSet columnResultSet = dbMeta.getColumns(null, dbName, tableName, "%");
        while (columnResultSet.next()) {
            String columnName = columnResultSet.getString("COLUMN_NAME");
            String columnType = columnResultSet.getString("TYPE_NAME");
            String remark = columnResultSet.getString("REMARKS");
            Integer length = columnResultSet.getInt("COLUMN_SIZE");
            Integer digitsLength = columnResultSet.getInt("DECIMAL_DIGITS");
            columns.add(new ColumnMetaData(columnName, columnType, remark, length, digitsLength));
        }
        return columns;
    }

    public static List<PrimaryKeyMetaData> getPrimaryKeys(DatabaseMetaData dbMeta, String dbName, String tableName) throws SQLException {
        List<PrimaryKeyMetaData> pkList = new ArrayList<>();
        ResultSet primaryKeySet = dbMeta.getPrimaryKeys(null, null, tableName);
        while (primaryKeySet.next()) {
            String columnName = primaryKeySet.getString("COLUMN_NAME");
            //Oracle结果中无字段TYPE_NAME
            pkList.add(new PrimaryKeyMetaData(columnName, "TYPE_NAME"));
        }
        return pkList;
    }

    public static List<ForeignKeyMetaData> getForeignKeys(DatabaseMetaData dbMeta, String dbName, String tableName) throws SQLException {
        List<ForeignKeyMetaData> foreignKeys = new LinkedList<>();
        ResultSet foreignKeysResultSet = dbMeta.getImportedKeys(dbName, dbName.toUpperCase(), tableName);
        while (foreignKeysResultSet.next()) {
            String fkTableName = foreignKeysResultSet.getString("FKTABLE_NAME");
            String fkColumnName = foreignKeysResultSet.getString("FKCOLUMN_NAME");
            String fkName = foreignKeysResultSet.getString("FK_NAME");
            String pkTableName = foreignKeysResultSet.getString("PKTABLE_NAME");
            String pkColumnName = foreignKeysResultSet.getString("PKCOLUMN_NAME");
            foreignKeys.add(new ForeignKeyMetaData(pkTableName, pkColumnName, fkTableName, fkColumnName, fkName));
        }
        return foreignKeys;
    }

    /**
     * 清理数据库操作
     * @param connection    连接
     * @param dbType        数据库类型
     * @throws SQLException sql 异常
     */
    public static void cleanDatabase(Connection connection, DBType dbType) throws SQLException {
        List<TableMetaData> tables = getTables(connection, dbType);
        // 删除外键
        for (TableMetaData table : tables) {
            dropForeignKeys(connection, table);
        }
        // 删除表数据
        for (TableMetaData table : tables) {
            deleteAll(connection, table.getName());
        }
        // 创建外键
        for (TableMetaData table : tables) {
            addForeignKeys(connection, table);
        }
    }

    public static void deleteAll(Connection connection, String tableName) throws SQLException {
        PreparedStatement stmt = connection.prepareStatement(String.format("delete from %s", tableName));
        stmt.execute();
        stmt.close();
    }

    public static void addForeignKeys(Connection connection, TableMetaData tableMetaData) throws SQLException {
        for (ForeignKeyMetaData foreignKey : tableMetaData.getForeignKeys()) {
            addForeignKey(connection, foreignKey);
        }
    }

    @VisibleForTesting
    public static void addForeignKey(Connection connection, ForeignKeyMetaData foreignKey) throws SQLException {
        String sql = String.format("alter table `%s` add constraint `%s` foreign  key (`%s`) references `%s`(`%s`)", foreignKey.getFkTable(), foreignKey.getFkName(), foreignKey.getFkColumn(), foreignKey.getPkTable(), foreignKey.getPkColumn());
        PreparedStatement stmt = connection.prepareStatement(sql);
        stmt.execute();
        stmt.close();
    }


    public static void dropForeignKeys(Connection connection, TableMetaData tableMetaData) throws SQLException {
        for (ForeignKeyMetaData foreignKey : tableMetaData.getForeignKeys()) {
            dropForeignKey(connection, foreignKey);
        }
    }

    @VisibleForTesting // 私有方法可用于测试
    private static void dropForeignKey(Connection connection, ForeignKeyMetaData foreignKey) throws SQLException {
        PreparedStatement stmt = connection.prepareStatement(String.format("alter table `%s` drop  foreign key `%s` ", foreignKey.getFkTable(), foreignKey.getFkName()));
        stmt.execute();
        stmt.close();
    }
    public static List<String> getCatalogs(Connection connection) throws SQLException {
        Assert.isTrue(!connection.isClosed(), "connection is closed");
        List<String> catalogs = new LinkedList<>();
        ResultSet catalogResultSet = connection.getMetaData().getCatalogs();
        while (catalogResultSet.next()) {
            catalogs.add(catalogResultSet.getString("TABLE_CAT"));
        }
        return catalogs;
    }
}
